﻿using PKTools.Database;
using System.Collections.Generic;

namespace PKTools.Generator
{
    public class MsSqlGenerator
    {
        public static Dictionary<int, string>
            Types;
        public List<SysObject>
            Tables;
        public List<SysFK>
            FKs;
        public List<SysProcedure>
            Procedures;

        public MsSqlGenerator(string connection)
        {
            using (new Stoper("generator load"))
            using (var sql = new MsSql(false, connection))
            {
                // types
                Types = sql.LoadDictionary<int, string>("SELECT user_type_id, name FROM sys.types");

                // tables
                this.Tables = sql.LoadBy<SysObject>(e => e.Type == "U");

                foreach (var t in this.Tables)
                {
                    t.Columns = sql.LoadBy<SysColumn>(e => e.ObjectID == t.ID);

                    sql.Clear();
                    sql.AddInput("ID", t.ID);

                    // primary keys
                    var
                        pks = sql.LoadList<int>(@"
                            SELECT 
                                ic.column_id
                            FROM
	                            sys.indexes i
	                            JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                            WHERE
                                i.is_primary_key = 1 AND
	                            i.object_id = @ID").ToArray();

                    foreach (var c in t.Columns)
                        c.IsPK = c.ID.In(pks);
                }

                // foreign keys
                this.FKs = sql.LoadAll<SysFK>();
                foreach (var fk in this.FKs)
                {
                    sql.Clear();
                    sql.AddInput("ID", fk.ID);
                    fk.ColMap = sql.LoadDictionary<string, string>(@"
                        SELECT 
                            COL_NAME(parent_object_id, parent_column_id), 
                            COL_NAME(referenced_object_id, referenced_column_id)
                        FROM 
                            sys.foreign_key_columns
                        WHERE
                            constraint_object_id = @ID");
                }

                // procedures
                this.Procedures = sql.LoadAll<SysProcedure>();

                this.Procedures.ForEach(p => p.Parameters = sql.LoadBy<SysParameter>(e => e.ObjectID == p.ID));
            }
        }
    }
}
